2016-11-28 - 8911 - Spec - Material SPS #MasterData
SPECIFICATIONS
8911 - Material SPS
Purpose
Need to create new report for Materials to get UPC's & Grids.
Admin Info
Purpose
|
Need to create new report for Materials to get UPC's & Grids.
|
Requested By
|
Mark Harris
|
Spec Created By
|
Venkateswara Reddy D
|
Spec Created Date
|
11/28/2016
|
Spec QA by
|
Uday P
|
Objects
|
ZCOPA_C03_SPS and MEAN Table
|
Document Status
|
Complete
|
Estimates
SNo
|
Activity
|
Estimation in Hours
|
1
|
Research
|
20
|
2
|
Documentation
|
10
|
3
|
Development/ Config
|
50
|
6
|
Other activity
|
20
|
|
TOTAL
|
100
|
References
Prior Tickets
None
Documents
Initial requirement with requested fields attached here:
Material Item Attributes Doc.xlsx
Requested Changes with the ITEM MASTER File in the attached document:
SPS Item Master Example Rev 11-22-16 - Includes SPS Modifications.xlsx
List of Customers to restrict for Sales Org 1000 is attached here:
SPS Customers.xlsx
Spec Change
SNo
|
Change Details
|
Requested By
|
Requested On
|
Notes if any
|
1
|
Need to get SPS data for 2015 and 2016.
|
Mark Harris
|
11/09/2016
|
Email
|
2
|
Need to Add the description for the required fields.
|
Mark Harris
|
11/23/2016
|
Email
|
3
|
Need to get SPS data for Sales org 1000.
|
Mark Harris
|
12/08/2016
|
Email
|
4
|
Need to get SPS data for requested customers.
|
Mark Harris
|
12/21/2016
|
Email
|
5
|
Need to remove the Key fields.
|
Mark Harris
|
12/21/2016
|
Email
|
6
|
Need to add delimiter to the file
|
Mark Harris
|
04/03/2017
|
Email
|
7
|
Need to generate a CSV file and place in file system folder
|
Mark Harris
|
04/03/2017
|
Email
|
Functional Requirement
Purpose/WHY:
To get UPC and Grid values for Materials in single report for requested Customers in Sales Organization 1000.
In Scope:
Created with requested customers for all Material SPS Attributes as requested.
Out of Scope:
Season Group and Season field not required in report. Also List Price and MSRP are not available in BW.
Solution Summary
1. Created ZCOPA_C03_Q020 BW BEX query with all Material Attributes using Navigational Attributes and used this query to generate Crystal Report for the requested years in BI.
2. After generating the Crystal report we exported it to EXCEL.
3. Joined the generated COPA Material excel sheet by using left outer join to NEP MEAN table to extract UPC's and Grids from MEAN Table.
4.We have generated a Material SPS CSV file with delimiter separator.
5.We have exported delimiter CSV file to file system in \\neccgbobjdb\reportdatasources\Material_SPS folder.
Solution Details
1. Created ZCOPA_C03_Q020 BW BEX query with all Material Attributes using Navigational Attributes and used this query to generate Crystal Report for the requested years in BI.
2. After generating the Crystal report we exported it to EXCEL.
3. Joined the generated COPA Material excel sheet by using left outer join to NEP MEAN table to extract UPC's and Grids from MEAN Table for the years 2015 and 2016.
Issues
List Price and MSRP fields are not available in BW source and these columns are left blank in the report.